#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class OrdersDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new Orders instance filled with the DataReader's current record data 
	     /// </summary>
	     private Orders GetOrdersFromReader(IDataReader dr)
	     {
		     Orders obj = new Orders();
		     obj.OrderID = (dr["OrderID"] is DBNull) ? Int32.MinValue :  (Int32)dr["OrderID"];
		     obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue :  (DateTime)dr["AddedDate"];
		     obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty :  (String)dr["AddedBy"];
		     obj.StatusID = (dr["StatusID"] is DBNull) ? Int32.MinValue :  (Int32)dr["StatusID"];
		     obj.ShippingMethod = (dr["ShippingMethod"] is DBNull) ? String.Empty :  (String)dr["ShippingMethod"];
		     obj.SubTotal = (dr["SubTotal"] is DBNull) ? Decimal.MinValue :  (Decimal)dr["SubTotal"];
		     obj.Shipping = (dr["Shipping"] is DBNull) ? Decimal.MinValue :  (Decimal)dr["Shipping"];
		     obj.TaxCode = (dr["TaxCode"] is DBNull) ? String.Empty :  (String)dr["TaxCode"];
		     obj.ShippingName = (dr["ShippingName"] is DBNull) ? String.Empty :  (String)dr["ShippingName"];
		     obj.ShippingAddress = (dr["ShippingAddress"] is DBNull) ? String.Empty :  (String)dr["ShippingAddress"];
		     obj.ShippingPostalCode = (dr["ShippingPostalCode"] is DBNull) ? String.Empty :  (String)dr["ShippingPostalCode"];
		     obj.ShippingCity = (dr["ShippingCity"] is DBNull) ? String.Empty :  (String)dr["ShippingCity"];
		     obj.ShippingState = (dr["ShippingState"] is DBNull) ? String.Empty :  (String)dr["ShippingState"];
		     obj.ShippingCountry = (dr["ShippingCountry"] is DBNull) ? String.Empty :  (String)dr["ShippingCountry"];
		     obj.CustomerEmail = (dr["CustomerEmail"] is DBNull) ? String.Empty :  (String)dr["CustomerEmail"];
		     obj.CustomerPhone = (dr["CustomerPhone"] is DBNull) ? String.Empty :  (String)dr["CustomerPhone"];
		     obj.CustomerFax = (dr["CustomerFax"] is DBNull) ? String.Empty :  (String)dr["CustomerFax"];
		     obj.ShippedDate = (dr["ShippedDate"] is DBNull) ? DateTime.MinValue :  (DateTime)dr["ShippedDate"];
		     obj.TransactionID = (dr["TransactionID"] is DBNull) ? String.Empty :  (String)dr["TransactionID"];
		     obj.TrackingID = (dr["TrackingID"] is DBNull) ? String.Empty :  (String)dr["TrackingID"];
		     return obj;
	     }
	     #endregion

	     /// <summary>
	     /// Returns a collection of Orders with the data read from the input DataReader
	     /// </summary>
	     private List<Orders> GetOrdersCollectionFromReader(IDataReader dr)
	     {
		     List<Orders> obj = new List<Orders>();
		     while(dr.Read())
			     obj.Add(GetOrdersFromReader(dr));
		     return obj;
	     }


         public List<Orders> Search(string Keyword, int StatusID, string FromDate, string ToDate)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 string sql = "Select * from w4u_Orders where OrderID > 0 ";
                 if (Keyword.Trim().Length > 0)
                 {
                     sql = sql + " And( (OrderID Like N'%" + Keyword + "%') Or  (ShippingName Like N'%" + Keyword + "%')  ";
                     sql = sql + " Or (CustomerEmail Like N'%" + Keyword + "%') Or (CustomerPhone Like N'%" + Keyword + "%'))";
                 }

                 if (StatusID > 0)
                     sql = sql + " And(StatusID = " + StatusID + ") ";

                 if (Globals.IsDate(FromDate) && Globals.IsDate(ToDate))
                 {
                     sql = sql + " And(AddedDate >= '" + Convert.ToDateTime(FromDate) + "' AND AddedDate <= '" + Convert.ToDateTime(ToDate) + "') ";
                 }

                 sql = sql + " Order by OrderID Desc";

                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetOrdersCollectionFromReader(dr);
             }
         }


	     public int Insert(Orders obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Orders_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Direction = ParameterDirection.Output;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
		     myCommand.Parameters.Add("@StatusID", SqlDbType.Int).Value = obj.StatusID;
		     myCommand.Parameters.Add("@ShippingMethod", SqlDbType.NVarChar).Value = obj.ShippingMethod;
		     myCommand.Parameters.Add("@SubTotal", SqlDbType.Money).Value = obj.SubTotal;
		     myCommand.Parameters.Add("@Shipping", SqlDbType.Money).Value = obj.Shipping;
		     myCommand.Parameters.Add("@TaxCode", SqlDbType.VarChar).Value = obj.TaxCode;
		     myCommand.Parameters.Add("@ShippingName", SqlDbType.NVarChar).Value = obj.ShippingName;
		     myCommand.Parameters.Add("@ShippingAddress", SqlDbType.NVarChar).Value = obj.ShippingAddress;
		     myCommand.Parameters.Add("@ShippingPostalCode", SqlDbType.NVarChar).Value = obj.ShippingPostalCode;
		     myCommand.Parameters.Add("@ShippingCity", SqlDbType.NVarChar).Value = obj.ShippingCity;
		     myCommand.Parameters.Add("@ShippingState", SqlDbType.NVarChar).Value = obj.ShippingState;
		     myCommand.Parameters.Add("@ShippingCountry", SqlDbType.NVarChar).Value = obj.ShippingCountry;
		     myCommand.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar).Value = obj.CustomerEmail;
		     myCommand.Parameters.Add("@CustomerPhone", SqlDbType.NVarChar).Value = obj.CustomerPhone;
		     myCommand.Parameters.Add("@CustomerFax", SqlDbType.NVarChar).Value = obj.CustomerFax;
		     myCommand.Parameters.Add("@ShippedDate", SqlDbType.DateTime).Value = obj.ShippedDate;
		     myCommand.Parameters.Add("@TransactionID", SqlDbType.NVarChar).Value = obj.TransactionID;
		     myCommand.Parameters.Add("@TrackingID", SqlDbType.NVarChar).Value = obj.TrackingID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (int)myCommand.Parameters["@OrderID"].Value; 
		     }
	     }

	     public bool Update(Orders obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Orders_Update", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = obj.OrderID;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
		     myCommand.Parameters.Add("@StatusID", SqlDbType.Int).Value = obj.StatusID;
		     myCommand.Parameters.Add("@ShippingMethod", SqlDbType.NVarChar).Value = obj.ShippingMethod;
		     myCommand.Parameters.Add("@SubTotal", SqlDbType.Money).Value = obj.SubTotal;
		     myCommand.Parameters.Add("@Shipping", SqlDbType.Money).Value = obj.Shipping;
		     myCommand.Parameters.Add("@TaxCode", SqlDbType.VarChar).Value = obj.TaxCode;
		     myCommand.Parameters.Add("@ShippingName", SqlDbType.NVarChar).Value = obj.ShippingName;
		     myCommand.Parameters.Add("@ShippingAddress", SqlDbType.NVarChar).Value = obj.ShippingAddress;
		     myCommand.Parameters.Add("@ShippingPostalCode", SqlDbType.NVarChar).Value = obj.ShippingPostalCode;
		     myCommand.Parameters.Add("@ShippingCity", SqlDbType.NVarChar).Value = obj.ShippingCity;
		     myCommand.Parameters.Add("@ShippingState", SqlDbType.NVarChar).Value = obj.ShippingState;
		     myCommand.Parameters.Add("@ShippingCountry", SqlDbType.NVarChar).Value = obj.ShippingCountry;
		     myCommand.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar).Value = obj.CustomerEmail;
		     myCommand.Parameters.Add("@CustomerPhone", SqlDbType.NVarChar).Value = obj.CustomerPhone;
		     myCommand.Parameters.Add("@CustomerFax", SqlDbType.NVarChar).Value = obj.CustomerFax;
		     myCommand.Parameters.Add("@ShippedDate", SqlDbType.DateTime).Value = obj.ShippedDate;
		     myCommand.Parameters.Add("@TransactionID", SqlDbType.NVarChar).Value = obj.TransactionID;
		     myCommand.Parameters.Add("@TrackingID", SqlDbType.NVarChar).Value = obj.TrackingID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }


         public bool UpdateStatus(int OrderID, int StatusID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_Order_UpdateStatus", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
                 myCommand.Parameters.Add("@StatusID", SqlDbType.Int).Value = StatusID;
                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }

	     public bool Delete(Int32 OrderID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Orders_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the w4u_Orders with the specified ID
	     /// </summary>
	     public Orders GetOrdersByID(Int32 OrderID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Orders_GetOrdersByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetOrdersFromReader(dr) : null);
		     }
	     }

	     //Get All Orders
	     public List<Orders> GetAllOrders(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Orders_GetAllOrders", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetOrdersCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  Orders
	     public int GetCountOrders()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_Orders_GetCountOrders", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }



	 }
}
